/*
 * @author: Tran Huan
 * @Create on 30/3/2011
 * @description Lop ho tro su dung co so du lieu sqlite
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author GTVT
 */
public class DbSqlite {

    Connection conn;
    Statement stat;

    //Constructor ket noi voi co so du lieu dbName
    public DbSqlite(String dbName) throws Exception {
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
        stat = conn.createStatement();
    }

    /*
     *Description: Thuc thi cac lenh cap nhat co so du lieu
     */
    public void update(String s) throws Exception {
        stat.executeUpdate(s);
    }

    /*
    //@description: Thuc thi doan lenh truy van
    //@return: ket qua truy van
     */
    public ResultSet query(String s) throws Exception {
        return stat.executeQuery(s);
    }

    /*
     * @description: truy van du lieu tu table nameTable voi dieu kien s
     * @return: ket qua truy van
     */
    public ResultSet get(String nameTable, String s) throws Exception {
        String query = "select * from " + nameTable + " where " + s;
        return stat.executeQuery(query);
    }

    /*
     * @description: thuc hien them vao table nameTable voi cac du lieu trong s
     * @Example:  with Table STUDENT(ID primary key,NAME,BORN)
     *            insert("STUDENT","08511,Join,01/03/1989")       
     */
    public void insert(String nameTable, String s) throws Exception {

        int count = 0;
        int i = 1;
        StringBuilder tmp = new StringBuilder();
        for (i = 0; i < s.length(); ++i) {
            if (s.charAt(i) == ',') {
                ++count;
                tmp.append("?");
                tmp.append(",");
            }
        }
        tmp.append("?");
        PreparedStatement prep = conn.prepareStatement("insert or ignore into " + nameTable + " values(" + tmp.toString() + ");");
        i = 1;
        while (count > 0) {
            prep.setString(i++, s.substring(0, s.indexOf(",")));
            s = s.substring(s.indexOf(",") + 1);
            --count;
        }
        prep.setString(i, s);
        prep.addBatch();
        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);


    }

    public void insert(String nameTable, String s, char delimiter) throws Exception {

        int count = 0;
        int i = 1;
        StringBuilder tmp = new StringBuilder();
        for (i = 0; i < s.length(); ++i) {
            if (s.charAt(i) == delimiter) {
                ++count;
                tmp.append("?");
                tmp.append(",");
            }
        }
        tmp.append("?");
        PreparedStatement prep = conn.prepareStatement("insert or ignore into " + nameTable + " values(" + tmp.toString() + ");");
        i = 1;
        while (count > 0) {
            prep.setString(i++, s.substring(0, s.indexOf(delimiter)));
            s = s.substring(s.indexOf(delimiter) + 1);
            --count;
        }
        prep.setString(i, s);
        prep.addBatch();
        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);


    }

    //Dong ket noi voi co so du lieu
    public void close() throws Exception {
        conn.close();
    }
}
